﻿using NORM.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DevelopAssistant.Service
{
    public class CreateOrReplaceObject
    {
        public static string ToSnippetCode(string ObjectName, DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();

            string ProcedureName = "sp_helptext";
            System.Data.Common.DbParameter[] dbParameters = null;
            DataTable dt = new DataTable();

            using (var db = Utility.GetAdohelper(DatabaseServer))
            {
                switch (DatabaseServer.ProviderName)
                {
                    case "System.Data.Sql":
                    case "System.Data.SQL":
                        dbParameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@objname", ObjectName) };
                        dt = db.GetDataSet(CommandType.StoredProcedure, ProcedureName, dbParameters).Tables[0];
                        break;
                }       
               
                foreach (DataRow dr in dt.Rows)
                {
                    sp.Append("" + dr["Text"] + "");
                }

            } 

            return sp.Value;
        }

        public static string CreateNewProcedure(DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();

            switch (DatabaseServer.ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":

                    sp.Append("-- =============================================").Append(Environment.NewLine);
                    sp.Append("-- Author:		<Author,,Name>").Append(Environment.NewLine);
                    sp.Append("-- Create date: <Create Date,,>").Append(Environment.NewLine);
                    sp.Append("-- Description:	<Description,,>").Append(Environment.NewLine);
                    sp.Append("-- =============================================").Append(Environment.NewLine);

                    sp.Append("CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>").Append(Environment.NewLine);
                    sp.Append("	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, ").Append(Environment.NewLine);
                    sp.Append("	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>").Append(Environment.NewLine);
                    sp.Append("AS").Append(Environment.NewLine);
                    sp.Append("BEGIN").Append(Environment.NewLine);
                    sp.Append("AS").Append(Environment.NewLine);
                    sp.Append("	-- SET NOCOUNT ON added to prevent extra result sets from").Append(Environment.NewLine);
                    sp.Append("	-- interfering with SELECT statements.").Append(Environment.NewLine);
                    sp.Append("	SET NOCOUNT ON;").Append(Environment.NewLine);
                    sp.Append("	-- Insert statements for procedure here").Append(Environment.NewLine);
                    sp.Append("	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>").Append(Environment.NewLine);
                    sp.Append("END").Append(Environment.NewLine);                  

                    break;

                case "System.Data.Sqlite":
                    sp.Append("Sqlite 不支持存储过程").Append(Environment.NewLine);
                    break;
            }

            return sp.Value;
        }

        public static string CreateNewView(DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();
            sp.Append("CREATE VIEW <View_Name,sysname,ViewName> ").Append(Environment.NewLine);
            sp.Append(" AS").Append(Environment.NewLine);
            switch (DatabaseServer.ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":
                    sp.Append("	--select * from [dbo].[table]").Append(Environment.NewLine);
                    break;
                case "System.Data.Sqlite":
                    sp.Append("	--select * from [table]").Append(Environment.NewLine);
                    break;
                case "System.Data.PostgreSql":
                    sp.Append("	--select * from \"table\"").Append(Environment.NewLine);
                    break;
            }
            sp.Append("GO").Append(Environment.NewLine);
            return sp.Value;
        }

        public static string CreateNewFunction(DataBaseServer DatabaseServer, string Type)
        {
            StringPlus sp = new StringPlus();
            return sp.Value;
        }

        public static string CreateTop100Query(string ObjectName, DataBaseServer DatabaseServer)
        {
            StringPlus sp = new StringPlus();

            sp.Append("SELECT ");

            switch (DatabaseServer.ProviderName)
            {
                case "System.Data.Sql":
                case "System.Data.SQL":
                    sp.Append("TOP 100 * ").Append(System.Environment.NewLine);
                    sp.Append("FROM [" + ObjectName + "]");
                    break;
                case "System.Data.Sqlite":
                case "System.Data.SQLite":
                    sp.Append("* ").Append(System.Environment.NewLine);
                    sp.Append("FROM [" + ObjectName + "]").Append(System.Environment.NewLine);
                    sp.Append("LIMIT 100");
                    break;
                case "System.Data.PostgreSql":
                case "System.Data.PostgreSQL":
                    sp.Append("* ").Append(System.Environment.NewLine);
                    sp.Append("FROM \"" + ObjectName + "\"").Append(System.Environment.NewLine);
                    sp.Append("LIMIT 100");
                    break;
            }

            return sp.Value;
        }

    }
}
